/*
 * Author: Minh-Tuan Ta
 * Date: 11/6/11
 * Class: FriendDB
 * This class used to access the data from Friend table
 */
package data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.*;
import beans.*;

public class FriendDB extends DB {
	/*
	 * Author: Minh-Tuan Ta
	 * Date: 11/6/11
	 * Method: getRecords
	 * This method will get all the records in the Friend table
	 */
	public static synchronized ArrayList<Friend> getRecords()
	{
		Connection conn = null;
		ArrayList<Friend> friends = new ArrayList<Friend>();
		PreparedStatement statement = null;
		String sql = "SELECT userid, friendid\n" +
					 "FROM friend";
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			
			while (rs.next())
			{
				Friend friend = new Friend();
				setFriend(friend, rs);
				
				friends.add(friend);
			}
			
			rs.close();
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return friends;
	}
	
	/*
	 * Author: Minh-Tuan Ta
	 * Date: 11/6/11
	 * Method: getFriends
	 * This method will get all the friends of a user
	 */
	public static synchronized ArrayList<User> getFriends(int userid)
	{
		Connection conn = null;
		ArrayList<Integer> friendids = new ArrayList<Integer>();
		ArrayList<User> friends = new ArrayList<User>();
		PreparedStatement statement = null;
		String sql = "SELECT o.friendid\n" +
					 "FROM friend o\n" +
					 "WHERE o.userid = ? AND EXISTS (SELECT *\n" +
					 "FROM friend i\n" +
					 "WHERE i.userid = o.friendid AND i.friendid = o.userid)";
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, userid);
			
			ResultSet rs = statement.executeQuery();
			
			//get the ids of friends
			while (rs.next())
			{
				friendids.add(rs.getInt(1));
			}
			
			rs.close();
			statement.close();
			
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		//after have the array of ids, get the User objects
		friends = UserDB.getUsers(friendids);
		
		return friends;
	}
	
	/*
	 * Author: Minh-Tuan Ta
	 * Date: 11/6/11
	 * Method: getFriends
	 * This method will get all the friends of a user
	 */
	public static synchronized ArrayList<User> getFriendRequests(int userid)
	{
		Connection conn = null;
		ArrayList<Integer> friendids = new ArrayList<Integer>();
		ArrayList<User> friends = new ArrayList<User>();
		PreparedStatement statement = null;
		String sql = "SELECT o.userid\n" +
					 "FROM friend o\n" +
					 "WHERE o.friendid = ? AND NOT EXISTS (SELECT *\n" +
					 "FROM friend i\n" +
					 "WHERE i.userid = ? AND i.friendid = o.userid)";
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, userid);
			statement.setInt(2, userid);
			
			ResultSet rs = statement.executeQuery();
			
			//get the ids of friends
			while (rs.next())
			{
				friendids.add(rs.getInt(1));
			}
			
			rs.close();
			statement.close();
			
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		//after have the array of ids, get the User objects
		friends = UserDB.getUsers(friendids);
		
		return friends;
	}
	
	/*
	 * Author: Minh-Tuan Ta
	 * Date: 11/6/11
	 * Method: deleteFriend
	 * This method will delete a friend relationship using userid and friendid
	 */
	public static synchronized int deleteFriend(int userid, int friendid)
	{
		Connection conn = null;
		int status = 0;
		PreparedStatement statement = null;
		String sql = "DELETE FROM friend\n" +
					 "WHERE userid = ? AND friendid = ?";
		
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, userid);
			statement.setInt(2, friendid);
			status = statement.executeUpdate();
			
			statement.close();
			
			//delete the other way
			sql = "DELETE FROM friend\n" +
				  "WHERE userid = ? AND friendid = ?";
			statement = conn.prepareStatement(sql);
			statement.setInt(2, userid);
			statement.setInt(1, friendid);
			status += statement.executeUpdate();
			
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return status;
	}
	
	/*
	 * Author: Minh-Tuan Ta
	 * Date: 11/6/11
	 * Method: addFriend
	 * This method will add a friend relationship, but the user has to wait the other person
	 * to confirm the friend relationship to be considered friends
	 */
	public static synchronized int addFriend(int userid, int friendid)
	{
		Connection conn = null;
		int status = 0;
		PreparedStatement statement = null;
		String sql = "INSERT INTO friend(userid, friendid)\n" +
					 "VALUES(?, ?)";
		
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, userid);
			statement.setInt(2, friendid);
			
			status = statement.executeUpdate();
			
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return status;
	}
	
	public static synchronized boolean isFriendRequestSent(int userID, int friendID)
	{
		boolean r = false;
		
		Connection conn = null;
		int status = 0;
		PreparedStatement statement = null;
		String sql = "SELECT userid\n" +
					 "FROM friend\n" +
					 "WHERE userid = ? AND friendid = ?";
		
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, userID);
			statement.setInt(2, friendID);
			
			ResultSet rs = statement.executeQuery();
			
			if (rs.next())
			{
				r = true;
			}
			
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return r;
	}
	
	public static synchronized boolean isHasFriendRequest(int userID, int friendID)
	{
		boolean r = false;
		
		Connection conn = null;
		int status = 0;
		PreparedStatement statement = null;
		String sql = "SELECT userid\n" +
					 "FROM friend\n" +
					 "WHERE friendid = ? AND userid = ?";
		
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, userID);
			statement.setInt(2, friendID);
			
			ResultSet rs = statement.executeQuery();
			
			if (rs.next())
			{
				r = true;
			}
			
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return r;
	}
	
	public static synchronized boolean isFriend(int userID, int friendID)
	{
		boolean r = false;
		
		Connection conn = null;
		int status = 0;
		PreparedStatement statement = null;
		String sql = "SELECT o.userid\n" +
					 "FROM friend o\n" +
					 "WHERE o.userid = ? AND o.friendid = ? " +
					 "AND EXISTS (SELECT i.userid\n" +
					 "FROM friend i\n" +
					 "WHERE i.userid = ? AND i.friendid = ?)";
		
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, userID);
			statement.setInt(2, friendID);
			statement.setInt(4, userID);
			statement.setInt(3, friendID);
			
			ResultSet rs = statement.executeQuery();
			
			if (rs.next())
			{
				r = true;
			}
			
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return r;
	}
}
